# 增强的报表分析功能模块
from django.shortcuts import render, get_object_or_404
from django.contrib.auth.decorators import login_required, permission_required
from django.http import JsonResponse, HttpResponse
from django.db.models import Count, Sum, Avg, Q, F, Max, Min
from django.utils import timezone
from django.core.paginator import Paginator
from django.contrib import messages
from datetime import datetime, timedelta, date
from decimal import Decimal
import json
import pandas as pd
from io import BytesIO
import openpyxl
from openpyxl.styles import Font, Border, Side, Alignment, PatternFill
from openpyxl.chart import BarChart, LineChart, PieChart, Reference
from openpyxl.utils.dataframe import dataframe_to_rows

from .models import Asset, AssetCategory, Department, MaintenanceRecord, AssetBorrow, Consumable, InventoryLog, AssetLog
from .models_system import SystemParameter


class DecimalEncoder(json.JSONEncoder):
    """处理Decimal类型的JSON编码器"""
    def default(self, obj):
        if isinstance(obj, Decimal):
            return float(obj)
        return super(DecimalEncoder, self).default(obj)


@login_required
def advanced_analytics_dashboard(request):
    """高级分析仪表板"""
    # 获取时间范围参数
    date_range = request.GET.get('date_range', '30')
    custom_start = request.GET.get('start_date')
    custom_end = request.GET.get('end_date')
    
    # 计算时间范围
    end_date = timezone.now()
    if custom_start and custom_end:
        try:
            start_date = datetime.strptime(custom_start, '%Y-%m-%d')
            end_date = datetime.strptime(custom_end, '%Y-%m-%d')
        except ValueError:
            start_date = end_date - timedelta(days=int(date_range))
    else:
        start_date = end_date - timedelta(days=int(date_range))
    
    # 核心KPI指标
    kpi_data = calculate_kpi_metrics(start_date, end_date)
    
    # 趋势分析数据
    trend_data = calculate_trend_analysis(start_date, end_date)
    
    # 对比分析数据
    comparison_data = calculate_comparison_analysis(start_date, end_date)
    
    # 预测分析数据
    forecast_data = calculate_forecast_analysis()
    
    # 异常检测数据
    anomaly_data = detect_anomalies(start_date, end_date)
    
    context = {
        'kpi_data': kpi_data,
        'trend_data': json.dumps(trend_data, cls=DecimalEncoder),
        'comparison_data': json.dumps(comparison_data, cls=DecimalEncoder),
        'forecast_data': json.dumps(forecast_data, cls=DecimalEncoder),
        'anomaly_data': anomaly_data,
        'date_range': {
            'start': start_date.strftime('%Y-%m-%d'),
            'end': end_date.strftime('%Y-%m-%d')
        },
        'selected_range': date_range
    }
    
    return render(request, 'assets/analytics/advanced_dashboard.html', context)


def calculate_kpi_metrics(start_date, end_date):
    """计算KPI指标"""
    # 资产相关KPI
    total_assets = Asset.objects.count()
    active_assets = Asset.objects.filter(status='in_use').count()
    asset_utilization = (active_assets / total_assets * 100) if total_assets > 0 else 0
    
    # 维修相关KPI
    maintenance_records = MaintenanceRecord.objects.filter(
        created_at__range=[start_date, end_date]
    )
    avg_repair_time = maintenance_records.filter(
        status='completed'
    ).aggregate(
        avg_time=Avg(F('completed_at') - F('created_at'))
    )['avg_time']
    
    # 成本相关KPI
    total_asset_value = Asset.objects.aggregate(
        total=Sum('purchase_price')
    )['total'] or 0
    
    maintenance_cost = maintenance_records.aggregate(
        total=Sum('actual_cost')
    )['total'] or 0
    
    # 库存相关KPI
    total_consumables = Consumable.objects.count()
    low_stock_items = Consumable.objects.filter(
        quantity__lte=F('min_quantity')
    ).count()
    stock_health = ((total_consumables - low_stock_items) / total_consumables * 100) if total_consumables > 0 else 0
    
    return {
        'total_assets': total_assets,
        'asset_utilization': round(asset_utilization, 2),
        'avg_repair_time': avg_repair_time.days if avg_repair_time else 0,
        'total_asset_value': total_asset_value,
        'maintenance_cost': maintenance_cost,
        'stock_health': round(stock_health, 2),
        'low_stock_items': low_stock_items
    }


def calculate_trend_analysis(start_date, end_date):
    """计算趋势分析数据"""
    # 按月统计资产增长趋势
    asset_trend = []
    current_date = start_date
    while current_date <= end_date:
        month_end = min(current_date.replace(day=28) + timedelta(days=4), end_date)
        month_end = month_end - timedelta(days=month_end.day)
        
        asset_count = Asset.objects.filter(
            created_at__lte=month_end
        ).count()
        
        asset_trend.append({
            'date': current_date.strftime('%Y-%m'),
            'count': asset_count
        })
        
        current_date = month_end + timedelta(days=1)
    
    # 维修成本趋势
    maintenance_trend = MaintenanceRecord.objects.filter(
        created_at__range=[start_date, end_date]
    ).extra(
        select={'month': "DATE_FORMAT(created_at, '%%Y-%%m')"}
    ).values('month').annotate(
        total_cost=Sum('actual_cost'),
        count=Count('id')
    ).order_by('month')
    
    return {
        'asset_trend': asset_trend,
        'maintenance_trend': list(maintenance_trend)
    }


def calculate_comparison_analysis(start_date, end_date):
    """计算对比分析数据"""
    # 部门对比分析
    department_comparison = Department.objects.annotate(
        asset_count=Count('asset'),
        total_value=Sum('asset__purchase_price'),
        maintenance_cost=Sum('asset__maintenancerecord__actual_cost')
    ).values(
        'name', 'asset_count', 'total_value', 'maintenance_cost'
    )
    
    # 类别对比分析
    category_comparison = AssetCategory.objects.annotate(
        asset_count=Count('asset'),
        avg_value=Avg('asset__purchase_price'),
        maintenance_frequency=Count('asset__maintenancerecord')
    ).values(
        'name', 'asset_count', 'avg_value', 'maintenance_frequency'
    )
    
    return {
        'department_comparison': list(department_comparison),
        'category_comparison': list(category_comparison)
    }


def calculate_forecast_analysis():
    """计算预测分析数据"""
    # 简单的线性预测模型
    # 基于历史数据预测未来3个月的维修需求
    
    # 获取过去12个月的维修数据
    twelve_months_ago = timezone.now() - timedelta(days=365)
    monthly_maintenance = MaintenanceRecord.objects.filter(
        created_at__gte=twelve_months_ago
    ).extra(
        select={'month': "DATE_FORMAT(created_at, '%%Y-%%m')"}
    ).values('month').annotate(
        count=Count('id'),
        cost=Sum('actual_cost')
    ).order_by('month')
    
    # 简单的移动平均预测
    if len(monthly_maintenance) >= 3:
        recent_avg_count = sum([m['count'] for m in monthly_maintenance[-3:]]) / 3
        recent_avg_cost = sum([m['cost'] or 0 for m in monthly_maintenance[-3:]]) / 3
        
        forecast_data = []
        for i in range(1, 4):  # 预测未来3个月
            future_date = timezone.now() + timedelta(days=30*i)
            forecast_data.append({
                'month': future_date.strftime('%Y-%m'),
                'predicted_count': int(recent_avg_count),
                'predicted_cost': float(recent_avg_cost)
            })
    else:
        forecast_data = []
    
    return {
        'maintenance_forecast': forecast_data
    }


def detect_anomalies(start_date, end_date):
    """检测异常数据"""
    anomalies = []
    
    # 检测维修成本异常
    avg_maintenance_cost = MaintenanceRecord.objects.aggregate(
        avg_cost=Avg('actual_cost')
    )['avg_cost'] or 0
    
    high_cost_maintenance = MaintenanceRecord.objects.filter(
        created_at__range=[start_date, end_date],
        actual_cost__gt=avg_maintenance_cost * 2
    ).select_related('asset')
    
    for record in high_cost_maintenance:
        anomalies.append({
            'type': 'high_maintenance_cost',
            'description': f'资产 {record.asset.name} 维修成本异常高: ¥{record.actual_cost}',
            'severity': 'high',
            'date': record.created_at.strftime('%Y-%m-%d')
        })
    
    # 检测资产使用异常
    # 长期未使用的资产
    unused_assets = Asset.objects.filter(
        status='idle',
        updated_at__lt=timezone.now() - timedelta(days=90)
    )
    
    for asset in unused_assets:
        anomalies.append({
            'type': 'unused_asset',
            'description': f'资产 {asset.name} 长期未使用',
            'severity': 'medium',
            'date': asset.updated_at.strftime('%Y-%m-%d')
        })
    
    return anomalies


@login_required
def custom_report_builder(request):
    """自定义报表构建器"""
    if request.method == 'POST':
        # 处理报表构建请求
        report_config = json.loads(request.body)
        report_data = build_custom_report(report_config)
        return JsonResponse(report_data, encoder=DecimalEncoder)
    
    # 获取可用的数据源和字段
    data_sources = get_available_data_sources()
    
    context = {
        'data_sources': data_sources
    }
    
    return render(request, 'assets/analytics/custom_report_builder.html', context)


def get_available_data_sources():
    """获取可用的数据源"""
    return {
        'assets': {
            'name': '资产',
            'fields': [
                {'name': 'name', 'label': '资产名称', 'type': 'text'},
                {'name': 'asset_number', 'label': '资产编号', 'type': 'text'},
                {'name': 'purchase_price', 'label': '购买价格', 'type': 'number'},
                {'name': 'status', 'label': '状态', 'type': 'choice'},
                {'name': 'created_at', 'label': '创建时间', 'type': 'date'},
                {'name': 'category__name', 'label': '类别', 'type': 'text'},
                {'name': 'department__name', 'label': '部门', 'type': 'text'},
            ]
        },
        'maintenance': {
            'name': '维修记录',
            'fields': [
                {'name': 'maintenance_type', 'label': '维修类型', 'type': 'choice'},
                {'name': 'estimated_cost', 'label': '预估成本', 'type': 'number'},
                {'name': 'actual_cost', 'label': '实际成本', 'type': 'number'},
                {'name': 'status', 'label': '状态', 'type': 'choice'},
                {'name': 'created_at', 'label': '创建时间', 'type': 'date'},
                {'name': 'asset__name', 'label': '资产名称', 'type': 'text'},
            ]
        },
        'consumables': {
            'name': '耗材',
            'fields': [
                {'name': 'name', 'label': '耗材名称', 'type': 'text'},
                {'name': 'quantity', 'label': '当前数量', 'type': 'number'},
                {'name': 'min_quantity', 'label': '最小库存', 'type': 'number'},
                {'name': 'unit_price', 'label': '单价', 'type': 'number'},
                {'name': 'warehouse__name', 'label': '仓库', 'type': 'text'},
            ]
        }
    }


def build_custom_report(config):
    """构建自定义报表"""
    data_source = config.get('data_source')
    selected_fields = config.get('fields', [])
    filters = config.get('filters', [])
    group_by = config.get('group_by')
    aggregations = config.get('aggregations', [])
    
    # 根据数据源选择模型
    model_map = {
        'assets': Asset,
        'maintenance': MaintenanceRecord,
        'consumables': Consumable
    }
    
    model = model_map.get(data_source)
    if not model:
        return {'error': '无效的数据源'}
    
    # 构建查询
    queryset = model.objects.all()
    
    # 应用过滤器
    for filter_item in filters:
        field = filter_item.get('field')
        operator = filter_item.get('operator')
        value = filter_item.get('value')
        
        if operator == 'equals':
            queryset = queryset.filter(**{field: value})
        elif operator == 'contains':
            queryset = queryset.filter(**{f'{field}__icontains': value})
        elif operator == 'gt':
            queryset = queryset.filter(**{f'{field}__gt': value})
        elif operator == 'lt':
            queryset = queryset.filter(**{f'{field}__lt': value})
        elif operator == 'date_range':
            start_date, end_date = value.split(',')
            queryset = queryset.filter(**{f'{field}__range': [start_date, end_date]})
    
    # 应用分组和聚合
    if group_by:
        queryset = queryset.values(group_by)
        for agg in aggregations:
            field = agg.get('field')
            func = agg.get('function')
            if func == 'count':
                queryset = queryset.annotate(count=Count(field))
            elif func == 'sum':
                queryset = queryset.annotate(sum=Sum(field))
            elif func == 'avg':
                queryset = queryset.annotate(avg=Avg(field))
    else:
        queryset = queryset.values(*selected_fields)
    
    # 执行查询并返回结果
    data = list(queryset)
    
    return {
        'data': data,
        'total_count': len(data)
    }


@login_required
def real_time_dashboard(request):
    """实时数据仪表板"""
    # 获取实时数据
    real_time_data = get_real_time_metrics()
    
    context = {
        'real_time_data': json.dumps(real_time_data, cls=DecimalEncoder)
    }
    
    return render(request, 'assets/analytics/real_time_dashboard.html', context)


def get_real_time_metrics():
    """获取实时指标数据"""
    now = timezone.now()
    today = now.date()
    
    return {
        'current_time': now.isoformat(),
        'today_stats': {
            'new_assets': Asset.objects.filter(created_at__date=today).count(),
            'maintenance_requests': MaintenanceRecord.objects.filter(created_at__date=today).count(),
            'borrow_requests': AssetBorrow.objects.filter(created_at__date=today).count(),
            'inventory_changes': InventoryLog.objects.filter(created_at__date=today).count(),
        },
        'system_health': {
            'total_assets': Asset.objects.count(),
            'active_assets': Asset.objects.filter(status='in_use').count(),
            'maintenance_pending': MaintenanceRecord.objects.filter(status='pending').count(),
            'overdue_borrows': AssetBorrow.objects.filter(
                expected_return_date__lt=now,
                status='borrowed'
            ).count(),
        }
    }


@login_required
def export_advanced_report(request):
    """导出高级报表"""
    report_type = request.GET.get('type', 'comprehensive')
    format_type = request.GET.get('format', 'excel')
    
    if format_type == 'excel':
        return export_excel_report(report_type)
    elif format_type == 'pdf':
        return export_pdf_report(report_type)
    else:
        return JsonResponse({'error': '不支持的导出格式'})


def export_excel_report(report_type):
    """导出Excel报表"""
    wb = openpyxl.Workbook()
    
    if report_type == 'comprehensive':
        # 综合报表
        create_comprehensive_excel_report(wb)
    elif report_type == 'asset_analysis':
        # 资产分析报表
        create_asset_analysis_excel_report(wb)
    elif report_type == 'maintenance_analysis':
        # 维修分析报表
        create_maintenance_analysis_excel_report(wb)
    
    # 保存到内存
    output = BytesIO()
    wb.save(output)
    output.seek(0)
    
    # 返回响应
    response = HttpResponse(
        output.getvalue(),
        content_type='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'
    )
    response['Content-Disposition'] = f'attachment; filename="{report_type}_report_{timezone.now().strftime("%Y%m%d")}.xlsx"'
    
    return response


def create_comprehensive_excel_report(wb):
    """创建综合Excel报表"""
    # 删除默认工作表
    wb.remove(wb.active)
    
    # 创建概览工作表
    overview_ws = wb.create_sheet("系统概览")
    create_overview_sheet(overview_ws)
    
    # 创建资产统计工作表
    asset_ws = wb.create_sheet("资产统计")
    create_asset_statistics_sheet(asset_ws)
    
    # 创建维修分析工作表
    maintenance_ws = wb.create_sheet("维修分析")
    create_maintenance_analysis_sheet(maintenance_ws)
    
    # 创建库存状态工作表
    inventory_ws = wb.create_sheet("库存状态")
    create_inventory_status_sheet(inventory_ws)


def create_overview_sheet(ws):
    """创建概览工作表"""
    # 设置标题
    ws['A1'] = '固定资产管理系统 - 系统概览'
    ws['A1'].font = Font(size=16, bold=True)
    ws.merge_cells('A1:D1')
    
    # 基础统计
    ws['A3'] = '基础统计'
    ws['A3'].font = Font(size=14, bold=True)
    
    stats = [
        ['总资产数量', Asset.objects.count()],
        ['在用资产', Asset.objects.filter(status='in_use').count()],
        ['闲置资产', Asset.objects.filter(status='idle').count()],
        ['维修中资产', Asset.objects.filter(status='maintenance').count()],
        ['总耗材种类', Consumable.objects.count()],
        ['低库存耗材', Consumable.objects.filter(quantity__lte=F('min_quantity')).count()],
    ]
    
    for i, (label, value) in enumerate(stats, start=4):
        ws[f'A{i}'] = label
        ws[f'B{i}'] = value
    
    # 设置列宽
    ws.column_dimensions['A'].width = 20
    ws.column_dimensions['B'].width = 15


def create_asset_statistics_sheet(ws):
    """创建资产统计工作表"""
    # 按类别统计
    ws['A1'] = '按类别统计'
    ws['A1'].font = Font(size=14, bold=True)
    
    headers = ['类别名称', '资产数量', '总价值', '平均价值']
    for i, header in enumerate(headers, start=1):
        ws.cell(row=2, column=i, value=header).font = Font(bold=True)
    
    category_stats = AssetCategory.objects.annotate(
        asset_count=Count('asset'),
        total_value=Sum('asset__purchase_price'),
        avg_value=Avg('asset__purchase_price')
    ).values('name', 'asset_count', 'total_value', 'avg_value')
    
    for i, stat in enumerate(category_stats, start=3):
        ws.cell(row=i, column=1, value=stat['name'])
        ws.cell(row=i, column=2, value=stat['asset_count'])
        ws.cell(row=i, column=3, value=float(stat['total_value'] or 0))
        ws.cell(row=i, column=4, value=float(stat['avg_value'] or 0))


def create_maintenance_analysis_sheet(ws):
    """创建维修分析工作表"""
    ws['A1'] = '维修统计分析'
    ws['A1'].font = Font(size=14, bold=True)
    
    # 维修统计数据
    maintenance_stats = MaintenanceRecord.objects.values('status').annotate(
        count=Count('id'),
        total_cost=Sum('actual_cost'),
        avg_cost=Avg('actual_cost')
    )
    
    headers = ['状态', '数量', '总成本', '平均成本']
    for i, header in enumerate(headers, start=1):
        ws.cell(row=2, column=i, value=header).font = Font(bold=True)
    
    for i, stat in enumerate(maintenance_stats, start=3):
        ws.cell(row=i, column=1, value=stat['status'])
        ws.cell(row=i, column=2, value=stat['count'])
        ws.cell(row=i, column=3, value=float(stat['total_cost'] or 0))
        ws.cell(row=i, column=4, value=float(stat['avg_cost'] or 0))


def create_inventory_status_sheet(ws):
    """创建库存状态工作表"""
    ws['A1'] = '库存状态分析'
    ws['A1'].font = Font(size=14, bold=True)
    
    headers = ['耗材名称', '当前库存', '最小库存', '状态', '单价', '总价值']
    for i, header in enumerate(headers, start=1):
        ws.cell(row=2, column=i, value=header).font = Font(bold=True)
    
    consumables = Consumable.objects.select_related('warehouse').all()
    
    for i, consumable in enumerate(consumables, start=3):
        status = '正常' if consumable.quantity > consumable.min_quantity else '低库存'
        total_value = consumable.quantity * (consumable.unit_price or 0)
        
        ws.cell(row=i, column=1, value=consumable.name)
        ws.cell(row=i, column=2, value=consumable.quantity)
        ws.cell(row=i, column=3, value=consumable.min_quantity)
        ws.cell(row=i, column=4, value=status)
        ws.cell(row=i, column=5, value=float(consumable.unit_price or 0))
        ws.cell(row=i, column=6, value=float(total_value))